#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "

--稽核上层表数据量
select 1/count(*) from hive.edu_dws.ydh_dws_check_wide_timecnt;
select 1/count(*) from hive.edu_dwd.ydh_dim_studying_student;

delete from hive.edu_dm.ydh_dm_check_wide_timecnt where 1=1;
insert into hive.edu_dm.ydh_dm_check_wide_timecnt
with tmp as (
    select t1.class_id,
           coalesce(studying_student_count,0) as  studying_student_count,
           studying_date
           create_date,
           time_type,
           dt,
           studying_cnt,
           late_cnt,
           leave_cnt
    from hive.edu_dws.ydh_dws_check_wide_timecnt  t1
left join hive.edu_dwd.ydh_dim_studying_student  t2
       on t1.class_id =t2.class_id
      and t1.dt=t2.studying_date
      and t2.class_id is not null
      and t2.studying_date is not null
)
select
    create_date,
    class_id,
    time_type,
    dt,
    studying_student_count,
    studying_cnt,
    if(studying_student_count =0,0.00000,studying_cnt*1.00000/studying_student_count) as studying_pct,
    late_cnt,
    if(studying_student_count =0,0.00000,late_cnt*1.00000/studying_student_count) as late_pct,
    leave_cnt,
    if(studying_student_count =0,0.00000,leave_cnt*1.00000/studying_student_count) as leave_pct,
    studying_student_count -studying_cnt -late_cnt-leave_cnt  as truant_cnt,
    if(studying_student_count =0,0.00000,(studying_student_count -studying_cnt -late_cnt-leave_cnt)*1.00000/studying_student_count) as truant_pct
from tmp
;
"
a=$?
if [ ${a} -eq 0 ];then 
	echo 'Program running status return code:' ${a}
	echo '=============================打完收工！================================='
else 
	echo 'Program running status return code:' ${a}
	echo '=============================程序出BUG咯！=============================='
	fi